﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//------------//
using QLKTX1._0.DTO;
using QLKTX1._0.DB;
using System.Data.SqlClient;
using System.Data;
//....................
namespace QLKTX1._0.DTO
{
    class ChiTietKhungGiaNhaDAO
    {
        // kiem tra co ton tai chua
        public static bool isExistData(ChiTietKhungGiaNhaDTO ob)
        {
            ConnectDB con = new ConnectDB();

            string sql = "SELECT count(" + ob.MaKhungGia.MaKhungGia + ") as kq FROM ChiTiet_khungGiaNha WHERE maTang='"+ob.MaTang.MaTang+"' and maKhu='"+ob.MaKhu.MaKhu+"' and maLoaiPhong='"+ob.MaLoaiPhong.MaLoaiPhong+"' and maKhungGia='"+ob.MaKhungGia.MaKhungGia+"' ";

            DataTable dt = con.getDataTable(sql);

            if (Int32.Parse(dt.Rows[0]["kq"].ToString()) >= 1)//Du lieu co ton tai ko
            {
                return true;
            }          

            return false;
        }


        public static List<ChiTietKhungGiaNhaDTO> dsChiTietKhungGiaNha()
        {
            List<ChiTietKhungGiaNhaDTO> dsCTKGN = new List<ChiTietKhungGiaNhaDTO>();

            ConnectDB con = new ConnectDB();
            string  sql = " SELECT dbo.Khu.maKhu, dbo.Khu.tenKhu, dbo.Tang.maTang, dbo.Tang.tenTang, dbo.loaiPhong.maLoaiPhong, dbo.loaiPhong.tenLoaiPhong, dbo.khungGiaNha.maKhungGia,"; 
                    sql += " dbo.khungGiaNha.tenKhungGia, dbo.chiTiet_khungGiaNha.donGia";
                    sql += " FROM dbo.chiTiet_khungGiaNha INNER JOIN";
                    sql += " dbo.Khu ON dbo.chiTiet_khungGiaNha.maKhu = dbo.Khu.maKhu INNER JOIN";
                    sql += " dbo.Tang ON dbo.chiTiet_khungGiaNha.maTang = dbo.Tang.maTang INNER JOIN";
                    sql += " dbo.loaiPhong ON dbo.chiTiet_khungGiaNha.maLoaiPhong = dbo.loaiPhong.maLoaiPhong INNER JOIN";
                    sql += " dbo.khungGiaNha ON dbo.chiTiet_khungGiaNha.maKhungGia = dbo.khungGiaNha.maKhungGia";
            

            DataTable dt = con.getDataTable(sql);


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ChiTietKhungGiaNhaDTO ctkgn = new ChiTietKhungGiaNhaDTO();

                KhuDTO k = new KhuDTO();
                k.MaKhu = dt.Rows[i]["maKhu"].ToString();
                k.TenKhu = dt.Rows[i]["tenKhu"].ToString();

                TangDTO t = new TangDTO();
                t.MaTang = dt.Rows[i]["maTang"].ToString();
                t.tenTang = dt.Rows[i]["tenTang"].ToString();

                LoaiPhongDTO lp = new LoaiPhongDTO();
                lp.MaLoaiPhong = dt.Rows[i]["maLoaiPhong"].ToString();
                lp.TenLoaiPhong = dt.Rows[i]["tenLoaiPhong"].ToString();

                KhungGiaNhaDTO kgn = new KhungGiaNhaDTO();
                kgn.MaKhungGia = Int32.Parse (dt.Rows[i]["maKhungGia"].ToString());
                kgn.TenKhungGia = dt.Rows[i]["tenKhungGia"].ToString();

                ctkgn.MaTang = t;
                ctkgn.MaKhu = k;
                ctkgn.MaKhungGia = kgn;
                ctkgn.MaLoaiPhong = lp;

                ctkgn.DonGia = Convert.ToInt32(Double.Parse(dt.Rows[i]["donGia"].ToString()));

                dsCTKGN.Add(ctkgn );
            }

            return dsCTKGN;
        }
        //Ham Insert
        public static bool themChiTietKhungGiaNha(ChiTietKhungGiaNhaDTO k)
        {
            ConnectDB con = new ConnectDB();// dung DB

            return con.executeQuery("INSERT INTO chiTiet_khungGiaNha(maTang,maKhu,maLoaiPhong,maKhungGia,donGia) VALUES('" + k.MaTang.MaTang + "','" + k.MaKhu.MaKhu + "','" + k.MaLoaiPhong.MaLoaiPhong + "'," + k.MaKhungGia.MaKhungGia  + "," + k.DonGia  + ")");
        }
        //Ham Delete
        public static bool xoaChiTietKhungGiaNha(ChiTietKhungGiaNhaDTO k)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("DELETE FROM chiTiet_khungGiaNha WHERE maTang='" + k.MaTang.MaTang + "' and maKhu='" + k.MaKhu.MaKhu + "' and maLoaiPhong='" + k.MaLoaiPhong.MaLoaiPhong + "' and maKhungGia=" + k.MaKhungGia.MaKhungGia + "");
        }
        //Ham Update
        public static bool capNhatChiTietKhungGiaNha(ChiTietKhungGiaNhaDTO k)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("UPDATE chiTiet_khungGiaNha set donGia=" + k.DonGia + " WHERE maTang='" + k.MaTang.MaTang + "'and maKhu='" + k.MaKhu.MaKhu + "'and maLoaiPhong='" + k.MaLoaiPhong.MaLoaiPhong + "'and maKhungGia=" + k.MaKhungGia.MaKhungGia + "");
        }
    }
}
